from django.db import models
import json
from django.core.exceptions import ValidationError
from utils.models import CoreModel
from system.models import DictData, User
from utils.models import CommonStatus
from product.models import Product
from utils.dict_utils import get_dict_label_by_value, get_dict_data_by_type


# BOM类型字典值
BOM_TYPE_DICT = 'BOM_TYPE'

# BOM状态字典值
BOM_STATUS_DICT = 'BOM_STATUS'

# 物料类型字典值
MATERIAL_TYPE_DICT = 'MATERIAL_TYPE'

# 生命周期状态字典值
LIFECYCLE_STATUS_DICT = 'LIFECYCLE_STATUS'

# BOM版本管理状态字典值
BOM_VERSION_STATUS_DICT = 'BOM_VERSION_STATUS'

# 审批状态字典值
APPROVAL_STATUS_DICT = 'APPROVAL_STATUS'

# 替代物料状态字典值
ALT_MATERIAL_STATUS_DICT = 'ALT_MATERIAL_STATUS'

# BOM变更类型字典值
BOM_CHANGE_TYPE_DICT = 'BOM_CHANGE_TYPE'

# BOM成本类型字典值
BOM_COST_TYPE_DICT = 'BOM_COST_TYPE'

# BOM模拟类型字典值
BOM_SIMULATION_TYPE_DICT = 'BOM_SIMULATION_TYPE'

# BOM报表类型字典值
BOM_REPORT_TYPE_DICT = 'BOM_REPORT_TYPE'

# BOM权限类型字典值
BOM_PERMISSION_TYPE_DICT = 'BOM_PERMISSION_TYPE'

# BOM流程类型字典值
BOM_FLOW_TYPE_DICT = 'BOM_FLOW_TYPE'

# BOM流程状态字典值
BOM_FLOW_STATUS_DICT = 'BOM_FLOW_STATUS'

# BOM步骤类型字典值
BOM_STEP_TYPE_DICT = 'BOM_STEP_TYPE'

# BOM步骤状态字典值
BOM_STEP_STATUS_DICT = 'BOM_STEP_STATUS'

# 文档类型字典值
DOCUMENT_TYPE_DICT = 'DOCUMENT_TYPE'


class BillOfMaterials(CoreModel):
    """
    BOM基础表 (p_bom)
    
    存储产品的物料清单头部信息
    """
    bom_id = models.AutoField(primary_key=True, verbose_name="BOM ID")
    bom_code = models.CharField(max_length=50, unique=True, verbose_name="BOM编码")
    product = models.ForeignKey(Product, on_delete=models.CASCADE, verbose_name="产品")
    bom_type = models.CharField(max_length=20, default="MBOM", verbose_name="BOM类型")  # 使用字典值替代枚举
    current_version = models.CharField(max_length=20, default="1.0", verbose_name="当前版本")
    status = models.CharField(max_length=20, default="DRAFT", verbose_name="状态")  # 使用字典值替代枚举
    lifecycle_status = models.CharField(max_length=20, default="DESIGNING", verbose_name="生命周期状态")  # 使用字典值替代枚举
    effective_date = models.DateField(null=True, blank=True, verbose_name="生效日期")
    expiry_date = models.DateField(null=True, blank=True, verbose_name="失效日期")
    is_active = models.BooleanField(default=True, verbose_name="是否激活")
    description = models.TextField(null=True, blank=True, verbose_name="描述")
    cost = models.DecimalField(max_digits=14, decimal_places=4, null=True, blank=True, verbose_name="总成本")
    configuration_template = models.JSONField(null=True, blank=True, verbose_name="配置模板")
    routing_reference = models.CharField(max_length=100, null=True, blank=True, verbose_name="工艺路线引用")
    
    # 为了优化递归查询性能，添加物化路径字段
    materialized_path = models.CharField(max_length=500, null=True, blank=True, verbose_name="物化路径")
    
    class Meta:
        verbose_name = "物料清单(BOM)"
        verbose_name_plural = verbose_name
        db_table = "p_bom"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom_code']),
            models.Index(fields=['product']),
            models.Index(fields=['is_active']),
            models.Index(fields=['materialized_path']),  # 物化路径索引
        ]
    
    def __str__(self):
        return f"{self.product.name} - BOM {self.current_version}"
    
    def save(self, *args, **kwargs):
        """保存时自动生成BOM编码"""
        # 如果没有提供BOM编码，则自动生成
        if not self.bom_code or self.bom_code == "DEFAULT-BOM-CODE":
            # 获取产品SKU
            product_sku = self.product.sku if self.product else "UNKNOWN"
            # 生成BOM编码，格式为：BOM-{产品SKU}-{版本号}
            self.bom_code = f"BOM-{product_sku}-{self.current_version}"
            
            # 检查生成的编码是否已存在，如果存在则添加序号
            counter = 1
            original_bom_code = self.bom_code
            while BillOfMaterials.objects.filter(bom_code=self.bom_code).exists():
                self.bom_code = f"{original_bom_code}-{counter}"
                counter += 1
        
        super().save(*args, **kwargs)
    
    def get_bom_type_display(self):
        """获取BOM类型的显示名称"""
        return get_dict_label_by_value(BOM_TYPE_DICT, self.bom_type)
    
    def get_status_display(self):
        """获取BOM状态的显示名称"""
        return get_dict_label_by_value(BOM_STATUS_DICT, self.status)
    
    def get_lifecycle_status_display(self):
        """获取生命周期状态的显示名称"""
        return get_dict_label_by_value(LIFECYCLE_STATUS_DICT, self.lifecycle_status)


class BOMComponent(CoreModel):
    """
    BOM组件表 (p_bom_item)
    
    存储BOM的组件明细信息，包括物料、用量、损耗等
    """
    component_id = models.AutoField(primary_key=True, verbose_name="组件ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="所属BOM", related_name='components')
    parent_component = models.ForeignKey('self', on_delete=models.CASCADE, null=True, blank=True, verbose_name="父组件", related_name='subcomponents')
    material = models.ForeignKey(Product, on_delete=models.CASCADE, verbose_name="物料")
    quantity = models.DecimalField(max_digits=10, decimal_places=4, verbose_name="用量")
    unit = models.CharField(max_length=20, verbose_name="单位")
    usage_rate = models.DecimalField(max_digits=8, decimal_places=4, default=1.0, verbose_name="使用比例")
    scrap_rate = models.DecimalField(max_digits=6, decimal_places=4, default=0, verbose_name="损耗率")
    sequence = models.IntegerField(default=0, verbose_name="顺序号")
    unit_cost = models.DecimalField(max_digits=12, decimal_places=4, null=True, blank=True, verbose_name="单位成本")
    total_cost = models.DecimalField(max_digits=14, decimal_places=4, null=True, blank=True, verbose_name="总成本")
    material_type = models.CharField(max_length=20, default="RAW", verbose_name="物料类型")  # 使用字典值替代枚举
    operation_code = models.CharField(max_length=50, null=True, blank=True, verbose_name="工序代码")
    operation_name = models.CharField(max_length=100, null=True, blank=True, verbose_name="工序名称")
    workstation = models.CharField(max_length=100, null=True, blank=True, verbose_name="工作站")
    lead_time = models.IntegerField(null=True, blank=True, verbose_name="前置时间(分钟)")
    preferred_supplier_id = models.IntegerField(null=True, blank=True, verbose_name="首选供应商ID")
    supplier_part_number = models.CharField(max_length=100, null=True, blank=True, verbose_name="供应商料号")
    procurement_type = models.CharField(max_length=20, null=True, blank=True, verbose_name="采购类型")
    quality_standard = models.CharField(max_length=100, null=True, blank=True, verbose_name="质量标准")
    certification_requirements = models.TextField(null=True, blank=True, verbose_name="认证要求")
    attributes = models.JSONField(null=True, blank=True, verbose_name="属性数据")
    description = models.TextField(null=True, blank=True, verbose_name="描述")
    is_active = models.BooleanField(default=True, verbose_name="是否激活")
    lifecycle_status = models.CharField(max_length=20, default="DESIGNING", verbose_name="生命周期状态")  # 使用字典值替代枚举
    
    # 为了优化递归查询性能，添加层级和路径字段
    level = models.IntegerField(default=1, verbose_name="层级")
    path = models.CharField(max_length=500, null=True, blank=True, verbose_name="组件路径")
    
    class Meta:
        verbose_name = "BOM组件明细"
        verbose_name_plural = verbose_name
        db_table = "p_bom_item"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['material']),
            models.Index(fields=['parent_component']),
            models.Index(fields=['level']),
            models.Index(fields=['path']),  # 组件路径索引
        ]
    
    def save(self, *args, **kwargs):
        """保存时自动计算层级和路径"""
        if not self.parent_component:
            # 顶层组件
            self.level = 1
            self.path = f"/{self.component_id}"
        else:
            # 子组件
            self.level = self.parent_component.level + 1
            self.path = f"{self.parent_component.path}/{self.component_id}"
        
        # 自动计算总成本
        if self.unit_cost and self.quantity and self.usage_rate:
            self.total_cost = self.unit_cost * self.quantity * self.usage_rate
        
        super().save(*args, **kwargs)
    
    def get_material_type_display(self):
        """获取物料类型的显示名称"""
        return get_dict_label_by_value(MATERIAL_TYPE_DICT, self.material_type)
    
    def get_lifecycle_status_display(self):
        """获取生命周期状态的显示名称"""
        return get_dict_label_by_value(LIFECYCLE_STATUS_DICT, self.lifecycle_status)


class BOMVersion(CoreModel):
    """
    BOM版本表 (p_bom_ver)
    
    记录BOM的版本变更信息，支持完整的版本控制和变更追踪
    """
    version_id = models.AutoField(primary_key=True, verbose_name="版本ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='versions')
    version_number = models.CharField(max_length=20, verbose_name="版本号")
    status = models.CharField(max_length=20, default='DRAFT', verbose_name="版本状态")  # 使用字典值替代枚举
    change_reason = models.TextField(null=True, blank=True, verbose_name="变更原因")
    change_content = models.TextField(null=True, blank=True, verbose_name="变更内容摘要")
    approved_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="审批人")
    approved_at = models.DateTimeField(null=True, blank=True, verbose_name="审批时间")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='created_bom_versions', verbose_name="创建人")
    effective_date = models.DateField(null=True, blank=True, verbose_name="生效日期")
    expiry_date = models.DateField(null=True, blank=True, verbose_name="失效日期")
    is_current = models.BooleanField(default=False, verbose_name="是否当前版本")
    is_active = models.BooleanField(default=True, verbose_name="是否激活")
    configuration_changes = models.JSONField(null=True, blank=True, verbose_name="配置变更记录")
    
    class Meta:
        verbose_name = "BOM版本管理"
        verbose_name_plural = verbose_name
        db_table = "p_bom_ver"  # 使用简化后的表名
        unique_together = ('bom', 'version_number')
        indexes = [
            models.Index(fields=['bom', 'is_current']),
            models.Index(fields=['version_number']),
            models.Index(fields=['status']),
        ]
    
    def get_status_display(self):
        """获取版本状态的显示名称"""
        return get_dict_label_by_value(BOM_VERSION_STATUS_DICT, self.status)


class BOMVersionComponent(CoreModel):
    """
    BOM版本组件表 (p_bom_ver_item)
    
    存储每个BOM版本的组件快照，用于版本比较和回溯
    """
    id = models.AutoField(primary_key=True, verbose_name="ID")
    version = models.ForeignKey(BOMVersion, on_delete=models.CASCADE, verbose_name="关联版本", related_name='version_components')
    component = models.ForeignKey(BOMComponent, on_delete=models.CASCADE, verbose_name="关联组件")
    material = models.ForeignKey(Product, on_delete=models.CASCADE, verbose_name="物料")
    quantity = models.DecimalField(max_digits=10, decimal_places=4, verbose_name="用量")
    unit = models.CharField(max_length=20, verbose_name="单位")
    usage_rate = models.DecimalField(max_digits=8, decimal_places=4, default=1.0, verbose_name="使用比例")
    scrap_rate = models.DecimalField(max_digits=6, decimal_places=4, default=0, verbose_name="损耗率")
    sequence = models.IntegerField(default=0, verbose_name="顺序号")
    unit_cost = models.DecimalField(max_digits=12, decimal_places=4, null=True, blank=True, verbose_name="单位成本")
    total_cost = models.DecimalField(max_digits=14, decimal_places=4, null=True, blank=True, verbose_name="总成本")
    material_type = models.CharField(max_length=20, default="RAW", verbose_name="物料类型")  # 使用字典值替代枚举
    operation_code = models.CharField(max_length=50, null=True, blank=True, verbose_name="工序代码")
    operation_name = models.CharField(max_length=100, null=True, blank=True, verbose_name="工序名称")
    workstation = models.CharField(max_length=100, null=True, blank=True, verbose_name="工作站")
    lead_time = models.IntegerField(null=True, blank=True, verbose_name="前置时间(分钟)")
    preferred_supplier_id = models.IntegerField(null=True, blank=True, verbose_name="首选供应商ID")
    supplier_part_number = models.CharField(max_length=100, null=True, blank=True, verbose_name="供应商料号")
    procurement_type = models.CharField(max_length=20, null=True, blank=True, verbose_name="采购类型")
    quality_standard = models.CharField(max_length=100, null=True, blank=True, verbose_name="质量标准")
    certification_requirements = models.TextField(null=True, blank=True, verbose_name="认证要求")
    attributes = models.JSONField(null=True, blank=True, verbose_name="属性数据")
    description = models.TextField(null=True, blank=True, verbose_name="描述")
    is_active = models.BooleanField(default=True, verbose_name="是否激活")
    lifecycle_status = models.CharField(max_length=20, default="DESIGNING", verbose_name="生命周期状态")  # 使用字典值替代枚举
    
    # 为了优化递归查询性能，添加层级和路径字段
    level = models.IntegerField(default=1, verbose_name="层级")
    path = models.CharField(max_length=500, null=True, blank=True, verbose_name="组件路径")
    
    class Meta:
        verbose_name = "BOM版本组件"
        verbose_name_plural = verbose_name
        db_table = "p_bom_ver_item"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['version']),
            models.Index(fields=['component']),
            models.Index(fields=['material']),
            models.Index(fields=['level']),
            models.Index(fields=['path']),  # 组件路径索引
        ]
    
    def get_material_type_display(self):
        """获取物料类型的显示名称"""
        return get_dict_label_by_value(MATERIAL_TYPE_DICT, self.material_type)
    
    def get_lifecycle_status_display(self):
        """获取生命周期状态的显示名称"""
        return get_dict_label_by_value(LIFECYCLE_STATUS_DICT, self.lifecycle_status)


class BOMChangeLog(CoreModel):
    """
    BOM变更记录表 (p_bom_log)
    
    记录BOM的所有变更历史，支持审计和追溯
    """
    log_id = models.AutoField(primary_key=True, verbose_name="日志ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='change_logs')
    version = models.ForeignKey(BOMVersion, on_delete=models.CASCADE, null=True, blank=True, verbose_name="关联版本")
    component = models.ForeignKey(BOMComponent, on_delete=models.CASCADE, null=True, blank=True, verbose_name="关联组件")
    change_type = models.CharField(max_length=20, verbose_name="变更类型")  # 使用字典值替代枚举
    change_description = models.TextField(verbose_name="变更描述")
    old_value = models.JSONField(null=True, blank=True, verbose_name="变更前值")
    new_value = models.JSONField(null=True, blank=True, verbose_name="变更后值")
    changed_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="变更人")
    change_time = models.DateTimeField(auto_now_add=True, verbose_name="变更时间")
    ip_address = models.GenericIPAddressField(null=True, blank=True, verbose_name="IP地址")
    user_agent = models.TextField(null=True, blank=True, verbose_name="用户代理")
    
    class Meta:
        verbose_name = "BOM变更记录"
        verbose_name_plural = verbose_name
        db_table = "p_bom_log"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['version']),
            models.Index(fields=['component']),
            models.Index(fields=['change_type']),
            models.Index(fields=['changed_by']),
            models.Index(fields=['change_time']),
        ]
    
    def get_change_type_display(self):
        """获取变更类型的显示名称"""
        return get_dict_label_by_value(BOM_CHANGE_TYPE_DICT, self.change_type)


class BOMCost(CoreModel):
    """
    BOM成本表 (p_bom_cost)
    
    存储BOM的成本计算结果，支持多版本成本比较
    """
    cost_id = models.AutoField(primary_key=True, verbose_name="成本ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='costs')
    version = models.ForeignKey(BOMVersion, on_delete=models.CASCADE, null=True, blank=True, verbose_name="关联版本")
    material_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="物料成本")
    labor_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="人工成本")
    overhead_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="制造费用")
    total_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="总成本")
    currency = models.CharField(max_length=10, default='CNY', verbose_name="货币")
    cost_date = models.DateField(verbose_name="成本日期")
    cost_method = models.CharField(max_length=50, default='STANDARD', verbose_name="成本计算方法")  # 使用字典值替代枚举
    notes = models.TextField(null=True, blank=True, verbose_name="备注")
    
    class Meta:
        verbose_name = "BOM成本"
        verbose_name_plural = verbose_name
        db_table = "p_bom_cost"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['version']),
            models.Index(fields=['cost_date']),
            models.Index(fields=['cost_method']),
        ]
    
    def get_cost_method_display(self):
        """获取成本计算方法的显示名称"""
        return get_dict_label_by_value(BOM_COST_TYPE_DICT, self.cost_method)


class BOMCostSimulation(CoreModel):
    """
    BOM成本模拟表 (p_bom_sim)
    
    存储BOM成本模拟结果，支持成本分析和优化
    """
    simulation_id = models.AutoField(primary_key=True, verbose_name="模拟ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='simulations')
    simulation_name = models.CharField(max_length=100, verbose_name="模拟名称")
    simulation_type = models.CharField(max_length=50, verbose_name="模拟类型")  # 使用字典值替代枚举
    base_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="基础成本")
    simulated_cost = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="模拟成本")
    cost_difference = models.DecimalField(max_digits=14, decimal_places=4, default=0, verbose_name="成本差异")
    cost_difference_percent = models.DecimalField(max_digits=6, decimal_places=2, default=0, verbose_name="成本差异百分比")
    simulation_parameters = models.JSONField(null=True, blank=True, verbose_name="模拟参数")
    simulation_results = models.JSONField(null=True, blank=True, verbose_name="模拟结果")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="创建人")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    
    class Meta:
        verbose_name = "BOM成本模拟"
        verbose_name_plural = verbose_name
        db_table = "p_bom_sim"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['simulation_type']),
            models.Index(fields=['created_by']),
            models.Index(fields=['created_at']),
        ]
    
    def __str__(self):
        return f"{self.bom.bom_code} - {self.simulation_name}"
    
    def get_simulation_type_display(self):
        """获取模拟类型的显示名称"""
        return get_dict_label_by_value(BOM_SIMULATION_TYPE_DICT, self.simulation_type)


class BOMReport(CoreModel):
    """
    BOM报表表 (p_bom_report)
    
    存储BOM报表生成记录和结果
    """
    report_id = models.AutoField(primary_key=True, verbose_name="报表ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='reports')
    report_name = models.CharField(max_length=100, verbose_name="报表名称")
    report_type = models.CharField(max_length=50, verbose_name="报表类型")  # 使用字典值替代枚举
    report_parameters = models.JSONField(null=True, blank=True, verbose_name="报表参数")
    report_content = models.JSONField(null=True, blank=True, verbose_name="报表内容")
    file_path = models.CharField(max_length=500, null=True, blank=True, verbose_name="文件路径")
    file_url = models.URLField(max_length=500, null=True, blank=True, verbose_name="文件URL")
    generated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="生成人")
    generated_at = models.DateTimeField(auto_now_add=True, verbose_name="生成时间")
    
    class Meta:
        verbose_name = "BOM报表"
        verbose_name_plural = verbose_name
        db_table = "p_bom_report"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['report_type']),
            models.Index(fields=['generated_by']),
            models.Index(fields=['generated_at']),
        ]
    
    def __str__(self):
        return f"{self.bom.bom_code} - {self.report_name}"
    
    def get_report_type_display(self):
        """获取报表类型的显示名称"""
        return get_dict_label_by_value(BOM_REPORT_TYPE_DICT, self.report_type)


class BOMPermission(CoreModel):
    """
    BOM权限表 (p_bom_perm)
    
    管理用户对BOM的访问权限
    """
    permission_id = models.AutoField(primary_key=True, verbose_name="权限ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='permissions')
    user = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name="关联用户")
    role = models.CharField(max_length=50, verbose_name="角色")
    permission_type = models.CharField(max_length=20, verbose_name="权限类型")  # 使用字典值替代枚举
    granted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='granted_bom_permissions', verbose_name="授权人")
    granted_at = models.DateTimeField(auto_now_add=True, verbose_name="授权时间")
    expires_at = models.DateTimeField(null=True, blank=True, verbose_name="过期时间")
    
    class Meta:
        verbose_name = "BOM权限"
        verbose_name_plural = verbose_name
        db_table = "p_bom_perm"  # 使用简化后的表名
        unique_together = ('bom', 'user', 'permission_type')
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['user']),
            models.Index(fields=['role']),
            models.Index(fields=['permission_type']),
            models.Index(fields=['granted_at']),
        ]
    
    def __str__(self):
        return f"{self.bom.bom_code} - {self.user.username} - {self.permission_type}"
    
    def get_permission_type_display(self):
        """获取权限类型的显示名称"""
        return get_dict_label_by_value(BOM_PERMISSION_TYPE_DICT, self.permission_type)


class BOMApprovalFlow(CoreModel):
    """
    BOM审批流程表 (p_bom_flow)
    
    管理BOM的审批流程
    """
    flow_id = models.AutoField(primary_key=True, verbose_name="流程ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='approval_flows')
    version = models.ForeignKey(BOMVersion, on_delete=models.CASCADE, null=True, blank=True, verbose_name="关联版本")
    flow_name = models.CharField(max_length=100, verbose_name="流程名称")
    flow_type = models.CharField(max_length=50, default='STANDARD', verbose_name="流程类型")  # 使用字典值替代枚举
    status = models.CharField(max_length=20, default='PENDING', verbose_name="流程状态")  # 使用字典值替代枚举
    initiator = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='initiated_bom_flows', verbose_name="发起人")
    initiated_at = models.DateTimeField(auto_now_add=True, verbose_name="发起时间")
    completed_at = models.DateTimeField(null=True, blank=True, verbose_name="完成时间")
    notes = models.TextField(null=True, blank=True, verbose_name="备注")
    
    class Meta:
        verbose_name = "BOM审批流程"
        verbose_name_plural = verbose_name
        db_table = "p_bom_flow"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['version']),
            models.Index(fields=['flow_type']),
            models.Index(fields=['status']),
            models.Index(fields=['initiator']),
            models.Index(fields=['initiated_at']),
        ]
    
    def __str__(self):
        return f"{self.bom.bom_code} - {self.flow_name}"
    
    def get_flow_type_display(self):
        """获取流程类型的显示名称"""
        return get_dict_label_by_value(BOM_FLOW_TYPE_DICT, self.flow_type)
    
    def get_status_display(self):
        """获取流程状态的显示名称"""
        return get_dict_label_by_value(BOM_FLOW_STATUS_DICT, self.status)


class BOMApprovalStep(CoreModel):
    """
    BOM审批步骤表 (p_bom_flow_step)
    
    管理BOM审批流程中的各个步骤
    """
    step_id = models.AutoField(primary_key=True, verbose_name="步骤ID")
    flow = models.ForeignKey(BOMApprovalFlow, on_delete=models.CASCADE, verbose_name="关联流程", related_name='steps')
    step_name = models.CharField(max_length=100, verbose_name="步骤名称")
    step_type = models.CharField(max_length=50, default='APPROVAL', verbose_name="步骤类型")  # 使用字典值替代枚举
    sequence = models.IntegerField(default=1, verbose_name="顺序号")
    approver = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="审批人")
    status = models.CharField(max_length=20, default='PENDING', verbose_name="步骤状态")  # 使用字典值替代枚举
    comments = models.TextField(null=True, blank=True, verbose_name="审批意见")
    approved_at = models.DateTimeField(null=True, blank=True, verbose_name="审批时间")
    is_required = models.BooleanField(default=True, verbose_name="是否必需")
    
    class Meta:
        verbose_name = "BOM审批步骤"
        verbose_name_plural = verbose_name
        db_table = "p_bom_flow_step"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['flow']),
            models.Index(fields=['step_type']),
            models.Index(fields=['approver']),
            models.Index(fields=['status']),
            models.Index(fields=['sequence']),
        ]
    
    def __str__(self):
        return f"{self.flow.flow_name} - {self.step_name}"
    
    def get_step_type_display(self):
        """获取步骤类型的显示名称"""
        return get_dict_label_by_value(BOM_STEP_TYPE_DICT, self.step_type)
    
    def get_status_display(self):
        """获取步骤状态的显示名称"""
        return get_dict_label_by_value(BOM_STEP_STATUS_DICT, self.status)


class AlternativeMaterial(CoreModel):
    """
    替代物料表
    
    存储组件的替代物料信息，用于供应链中断或成本优化
    """
    alt_material_id = models.AutoField(primary_key=True, verbose_name="替代物料ID")
    bom_component = models.ForeignKey(BOMComponent, on_delete=models.CASCADE, verbose_name="关联BOM组件", related_name='alternatives')
    alternative_material = models.ForeignKey(Product, on_delete=models.CASCADE, verbose_name="替代物料")
    substitution_ratio = models.DecimalField(max_digits=8, decimal_places=4, default=1.0, verbose_name="替代比例")
    priority = models.IntegerField(default=1, verbose_name="优先级")  # 新增优先级字段
    valid_from = models.DateField(null=True, blank=True, verbose_name="有效期开始")
    valid_until = models.DateField(null=True, blank=True, verbose_name="有效期结束")
    compatibility_score = models.DecimalField(max_digits=5, decimal_places=2, default=100.0, verbose_name="兼容性评分")
    approval_required = models.BooleanField(default=True, verbose_name="是否需要审批")
    is_active = models.BooleanField(default=True, verbose_name="是否有效")
    approval_status = models.CharField(max_length=20, default='PENDING', verbose_name="审批状态")  # 使用字典值替代枚举
    reason = models.TextField(null=True, blank=True, verbose_name="替代原因")
    notes = models.TextField(null=True, blank=True, verbose_name="备注")
    cost_impact = models.DecimalField(max_digits=10, decimal_places=4, null=True, blank=True, verbose_name="成本影响")
    performance_impact = models.TextField(null=True, blank=True, verbose_name="性能影响")
    
    class Meta:
        verbose_name = "替代物料"
        verbose_name_plural = verbose_name
        db_table = "product_alternative_material"
        indexes = [
            models.Index(fields=['bom_component']),
            models.Index(fields=['alternative_material']),
            models.Index(fields=['priority', 'approval_status']),  # 优先级和审批状态索引
            models.Index(fields=['valid_from', 'valid_until']),  # 有效期索引
        ]
    
    def __str__(self):
        return f"{self.bom_component} - {self.alternative_material.name}"
    
    def get_approval_status_display(self):
        """获取审批状态的显示名称"""
        return get_dict_label_by_value(ALT_MATERIAL_STATUS_DICT, self.approval_status)


class BOMDocument(CoreModel):
    """
    BOM关联文档表 (p_bom_document)
    
    管理BOM相关的文档信息
    """
    doc_id = models.AutoField(primary_key=True, verbose_name="文档ID")
    bom = models.ForeignKey(BillOfMaterials, on_delete=models.CASCADE, verbose_name="关联BOM", related_name='documents')
    version = models.ForeignKey(BOMVersion, on_delete=models.CASCADE, null=True, blank=True, verbose_name="关联版本")
    doc_type = models.CharField(max_length=50, default='SPECIFICATION', verbose_name="文档类型")  # 使用字典值替代枚举
    doc_name = models.CharField(max_length=200, verbose_name="文档名称")
    doc_path = models.CharField(max_length=500, verbose_name="文档路径")
    doc_size = models.IntegerField(null=True, blank=True, verbose_name="文档大小(KB)")
    upload_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, verbose_name="上传人")
    upload_time = models.DateTimeField(auto_now_add=True, verbose_name="上传时间")
    is_active = models.BooleanField(default=True, verbose_name="是否有效")
    
    class Meta:
        verbose_name = "BOM文档"
        verbose_name_plural = verbose_name
        db_table = "p_bom_document"  # 使用简化后的表名
        indexes = [
            models.Index(fields=['bom']),
            models.Index(fields=['version']),
            models.Index(fields=['doc_type']),
            models.Index(fields=['upload_user']),
            models.Index(fields=['upload_time']),
        ]
    
    def __str__(self):
        return f"{self.bom.bom_code} - {self.doc_name}"
    
    def get_doc_type_display(self):
        """获取文档类型的显示名称"""
        return get_dict_label_by_value(DOCUMENT_TYPE_DICT, self.doc_type)